package board;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;

public class Board {

	// 첨부파일
	int fileSize = 10 * 1024 * 1024;
	String uploadPath = "C:\\Users\\jhta\\Desktop\\1403\\svn_jobs\\WebContent\\upload";
	String fileEncoding = "UTF-8";

	// jdbc연결
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@192.168.10.122:1521:xe";
	String db_user = "jobs";
	String db_pwd = "1234";

	Connection conn;

	BoardVo vo;
	ComenVo cvo;

	//페이지 분리 관련된 필드
	private int listSize = 10; // 한페이지 분리에 보여질 행수
	private int blockSize = 5; // 바닥에 보이게할 페이지수
	
	private int totSize = 0; // 검색된 데이터 총 개수
	private int totPage = 0; // 전체 페이지 수
	private int totBlock = 0; //전체 블록수
	
	private int nowPage = 1; //현재 페이지
	private int nowBlock = 0; //현제 블록
	
	private int startNo = 1; //목록의 시작 위치 초기값
	private int endNo = 10; //목록의 끝위치 초기값 (listSize와 같다)
	private int startPage = 1; //현재 블록의 시작 페이지 번호 초기값
	private int endPage = 5; //현재 블록의 끝 페이지 번호 초기값(blockSize와 같다)

	public Board() {
		try {
			// 1) 드라이버 로딩
			Class.forName(driver).newInstance();

			// 2) Connection 객체생성
			conn = DriverManager.getConnection(url, db_user, db_pwd);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void computePage(){
		totPage = (int)Math.ceil(totSize*1.0 / listSize);
		totBlock = (int)Math.ceil(totPage*1.0 / blockSize);
		
		nowBlock = (int)Math.ceil(nowPage*1.0 / blockSize); 
		
		endPage = nowBlock * blockSize; 
		startPage = endPage - blockSize + 1;
		if(totPage < endPage) endPage = totPage; 
		
		endNo = nowPage * listSize;
		startNo = endNo -listSize+1;
		if(totSize < endNo) endNo = totSize;

	}
	
	public Vector<BoardVo> list() {
		Vector<BoardVo> v = new Vector<>();
		String sql = "";
		PreparedStatement ps =null;
		ResultSet rs = null;
		
		try {
			sql = "select count(serial) cnt from board"
					+ " where (mid like ? or subject like ? or content like ?)"
					+ " and part=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, "%" + vo.getFind() + "%");
			ps.setString(2, "%" + vo.getFind() + "%");
			ps.setString(3, "%" + vo.getFind() + "%");
			ps.setString(4, vo.getPart());
			rs = ps.executeQuery();
			rs.next();

			totSize =  rs.getInt("cnt");
			computePage();//페이지와 관련된 계산
			
			sql = "	select * from "
					+ "			(select rownum no, a.* from ("
					+ "				select * from board where (mid like ? or subject like ? or content like ?)"
					+ " 			and part = ? order by grp desc, deep"
					+ "			) a"
					+ "		)where no between ? and ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, "%" + vo.getFind() + "%");
			ps.setString(2, "%" + vo.getFind() + "%");
			ps.setString(3, "%" + vo.getFind() + "%");
			ps.setString(4, vo.getPart());
			ps.setInt(5, startNo);
			ps.setInt(6, endNo);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				BoardVo bv = new BoardVo();

				bv.setSerial(rs.getInt("serial"));
				bv.setSubject(rs.getString("subject"));
				bv.setMid(rs.getString("mid"));
				bv.setMdate(rs.getDate("mdate"));
				bv.setHit(rs.getInt("hit"));
				bv.setDeep(rs.getString("deep"));
				v.add(bv);
			}

			rs.close();
			ps.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return v;
	}

	public boolean insert() {
		boolean flag = false;
		try {
			MultipartRequest mr = new MultipartRequest(vo.getRequest(),
					uploadPath, fileSize, fileEncoding,
					new DefaultFileRenamePolicy());

			String part = mr.getParameter("part");
			String mid = mr.getParameter("mid");
			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");
			String pwd = mr.getParameter("pwd");

			String orifile = mr.getOriginalFileName("filename");
			String sysfile = mr.getFilesystemName("filename");

			if (sysfile == null) {
				sysfile = "NoFile";
			}

			// db저장
			String sql = "insert all"
					+ " into board(serial, part, mid, mdate, pwd, subject, hit, content, grp, deep)"
					+ " values(brd_serial.nextval, ?, ?, sysdate, ?, ?, 0, ?, brd_serial.currval, brd_serial.currval)";
			if (!"NoFile".equals(sysfile)) {
				sql += " into brdfiles(serial, fileid, filename)"
						+ " values(bfile_serial.nextval, brd_serial.currval,?)";
			}
			sql += " select * from dual";// 이 셀렉이 문장이있어야 파일이 들어간다.

			try {
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, part);
				ps.setString(2, mid);
				ps.setString(3, pwd);
				ps.setString(4, subject);
				ps.setString(5, content);
				if (!"NoFile".equals(sysfile)) {
					ps.setString(6, sysfile);
				}

				int cnt = ps.executeUpdate();
				if (cnt > 0) {
					flag = true;
				}
				ps.close();
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}

		return flag;
	}

	public BoardVo view() {
		BoardVo bv = new BoardVo();
		String sql = null;
		PreparedStatement ps = null;

		try {

			sql = "update board set hit = hit+1 where serial=" + vo.getSerial();

			ps = conn.prepareStatement(sql);
			ps.executeUpdate();

			sql = "select b.*, f.filename"
					+ " from board b left outer join brdfiles f"
					+ " on b.serial = f.fileid" + " where b.serial=?";

			ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				bv.setSerial(rs.getInt("serial"));
				bv.setMid(rs.getString("mid"));
				bv.setMdate(rs.getDate("mdate"));
				bv.setSubject(rs.getString("subject"));
				bv.setContent(rs.getString("content"));
				String str = rs.getString("filename");
				if (str == null) {
					str = "NoFile";
				}
				bv.setFilename(str);
				bv.setGrp(rs.getInt("grp"));
				bv.setHit(rs.getInt("hit"));
				bv.setDeep(rs.getString("deep"));
				bv.setPwd(rs.getString("pwd"));
				// grp, deep, hit
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return bv;
	}

	public boolean delete() {
		boolean flag = false;
		PreparedStatement ps = null;
		String s="";
		try {
			if (!"NoFile".equals(vo.getFilename())) {
				s = "delete from brdfiles where fileid="
						+ vo.getSerial();
				ps = conn.prepareStatement(s);
				ps.executeUpdate();

			}
			s= "delete from brd_comment where commentid =?";
			ps = conn.prepareStatement(s);
			ps.setInt(1, vo.getSerial());
			ps.executeUpdate();
			
			String sql = "delete from board where serial=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());

			int cnt = ps.executeUpdate();
			if (cnt > 0) {
				flag = true;
				File file = new File(uploadPath + "/" + vo.getFilename());
				if (file.exists()) {
					file.delete();
				} else {
					// 고민
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return flag;
	}

	public boolean modify() {
		boolean flag = false;

		try {
			MultipartRequest mr = new MultipartRequest(vo.getRequest(),
					uploadPath, fileSize, fileEncoding,
					new DefaultFileRenamePolicy());

			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");
			String pwd = mr.getParameter("pwd");

			String oldfile = mr.getParameter("filename");
			String sysfile = mr.getFilesystemName("newfilename");
			int serial = Integer.parseInt(mr.getParameter("serial"));
			String fileDelFlag = mr.getParameter("fileDelFlag");

			if (sysfile == null) {
				sysfile = "NoFile";
			}

			// 데이터베이스 수정
			PreparedStatement ps = null;
			String sql = null;

			// 기존 첨부된 파일을 삭제
			if (fileDelFlag.equals("del")) {
				fileDel(oldfile, serial);
			}

			// 자동 커밋을 방지
			conn.setAutoCommit(false);

			sql = "update board set subject=?, content=? where pwd=? and serial=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, subject);
			ps.setString(2, content);
			ps.setString(3, pwd);
			ps.setInt(4, serial);
			int cnt = ps.executeUpdate();
			if (cnt > 0) {
				flag = true;
			}

			// 첨부파일이 변경된 경우
			if (!("NoFile".equals(sysfile))) {
				sql = "insert into brdfiles(serial, fileid, filename)"
						+ " values(bfile_serial.nextval, ?, ?)";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, serial);
				ps.setString(2, sysfile);
				cnt = ps.executeUpdate();
				if (cnt > 0) {
					conn.commit();
				} else {
					conn.rollback();
					flag = false;
				}
			} else {
				conn.commit();
			}

			ps.close();
			conn.close();

		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException se) {
			se.printStackTrace();
		}

		return flag;
	}

	public boolean repInsert() {
		boolean flag = false;
		try {
			MultipartRequest mr = new MultipartRequest(vo.getRequest(),
					uploadPath, fileSize, fileEncoding,
					new DefaultFileRenamePolicy());

			String mid = mr.getParameter("mid");
			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");
			String pwd = mr.getParameter("pwd");
			String part = mr.getParameter("part");

			String orifile = mr.getOriginalFileName("filename");
			String sysfile = mr.getFilesystemName("filename");

			int grp = Integer.parseInt(mr.getParameter("grp"));
			String deep = mr.getParameter("deep");

			if (sysfile == null) {
				sysfile = "NoFile";
			}

			// db저장
			String sql = "insert all"
					+ " into board(serial, part, mid, mdate, pwd, subject, hit, content, grp, deep)"
					+ " values(brd_serial.nextval, ?, ?, sysdate, ?, ?, 0, ?, ?, ?||brd_serial.currval)";
			if (!"NoFile".equals(sysfile)) {
				sql += " into brdfiles(serial, fileid, filename)"
						+ " values(bfile_serial.nextval, brd_serial.currval,?)";
			}
			sql += " select * from dual";// 이 셀렉이 문장이있어야 파일이 들어간다.

			try {
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, part);
				ps.setString(2, mid);
				ps.setString(3, pwd);
				ps.setString(4, "└RE:"+subject);
				ps.setString(5, content);
				ps.setInt(6, grp);
				ps.setString(7, deep + "-");

				if (!"NoFile".equals(sysfile)) {
					ps.setString(8, sysfile);
				}

				int cnt = ps.executeUpdate();
				if (cnt > 0) {
					flag = true;
				}
				ps.close();
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}

		return flag;
	}
	
	public int commentGet(int commentid){
		int k=0;
		try {
			String sql = "select count(commentid) cnt from brd_comment where commentid=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, commentid);
			ResultSet rs = ps.executeQuery();
			rs.next();
			k = rs.getInt("cnt");
			
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return k;
	}
	public void endConn(){
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public boolean comentInsert() {
		boolean flag = false;
		try {
			String sql = "insert into brd_comment(serial, mid, content,commentid) values(seq_comen.nextval, ?,?,?)";

			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, cvo.getMid());
			ps.setString(2, cvo.getContent());
			ps.setInt(3, cvo.getSerial());

			int cnt = ps.executeUpdate();
			if (cnt > 0) {
				flag = true;
			}
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return flag;
	}

	public Vector<ComenVo> comment() {
		Vector<ComenVo> v = new Vector<>();
		String sql = "select * from brd_comment where commentid = ?"
				+ " order by serial asc";

		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, cvo.getSerial());

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				ComenVo cv = new ComenVo();

				cv.setSerial(rs.getInt("serial"));
				cv.setMid(rs.getString("mid"));
				cv.setContent(rs.getString("content"));
				cv.setMdate(rs.getString("mdate"));
				cv.setCommentid(rs.getInt("commentid"));

				v.add(cv);
			}

			rs.close();
			ps.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
			e.getMessage();
		}
		return v;
	}

	public void fileDel(String oldfile, int serial) {
		String sql = "delete from brdfiles where fileid=? and filename=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, serial);
			ps.setString(2, oldfile);
			ps.executeUpdate();

			File file = new File(uploadPath + "/" + oldfile);
			if (file.exists()) {
				file.delete();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	
	//페이지 처리와 관련된 getter setter
	
	public int getListSize() {
		return listSize;
	}

	public void setListSize(int listSize) {
		this.listSize = listSize;
	}

	public int getBlockSize() {
		return blockSize;
	}

	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}

	public int getTotSize() {
		return totSize;
	}

	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}

	public int getTotPage() {
		return totPage;
	}

	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}

	public int getTotBlock() {
		return totBlock;
	}

	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}

	public int getNowPage() {
		return nowPage;
	}

	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}

	public int getNowBlock() {
		return nowBlock;
	}

	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}

	public int getStartNo() {
		return startNo;
	}

	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}

	public int getEndNo() {
		return endNo;
	}

	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}

	public BoardVo getVo() {
		return vo;
	}

	public void setVo(BoardVo vo) {
		this.vo = vo;
	}

	public ComenVo getCvo() {
		return cvo;
	}

	public void setCvo(ComenVo cvo) {
		this.cvo = cvo;
	}
	
	public String getUploadPath() {
		return uploadPath;
	}
	
	public void setUploadPath(String uploadPath) {
		this.uploadPath = uploadPath;
	}

}
